using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Company.BLL.WS.SXXK;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Company.BLL.SXXK
{
    public partial class DinhMuc
    {
        public DataSet getSanPhamDinhMuc(string maHaiQuan)
        {
            string sql = "SELECT Ma, Ten, MaHS, DVT_ID FROM t_SXXK_SanPham "+
                              "WHERE Ma IN (SELECT DISTINCT MaSanPham FROM t_SXXK_ThongTinDinhMuc WHERE maHaiQuan='" + maHaiQuan + "') ORDER BY MA";
            DbCommand dbc = db.GetSqlStringCommand(sql);
            DataSet ds = new DataSet();
            db.LoadDataSet(dbc, ds, "SanPham");
            
            sql = "SELECT a.MaSanPham , a.MaNguyenPhuLieu,b.Ten, b.maHS ,b.DVT_ID ,a.DinhMucSuDung,a.TyLeHaoHut FROM  t_SXXK_DinhMuc a " +
              "INNER JOIN t_SXXK_NguyenPhuLieu b ON a.MaNguyenPhuLieu = b.Ma " +
              " WHERE a.maHaiQuan='" + maHaiQuan + "' ORDER BY a.MaSanPham";
            DbCommand dbc1 = db.GetSqlStringCommand(sql);
            db.LoadDataSet(dbc1, ds, "tblNguyenPhuLieu");
            DataRelation rel = new DataRelation("rel",
                            ds.Tables[0].Columns[0],
                            ds.Tables[1].Columns[0], false);
            ds.Relations.Add(rel);
            return ds;
        }
        public DataSet WS_GetDanhSachDaDangKy(string maHaiQuan, string maDoanhNghiep)
        {
            SXXKService service = new SXXKService();
            return service.DinhMuc_GetDanhSach(maHaiQuan, maDoanhNghiep);
        }

        public bool UpdateRegistedToDatabase(string maHaiQuan, string maDoanhNghiep, DataSet ds)
        {
            DinhMucCollection collection = new DinhMucCollection();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        ThongTinDinhMuc ttdm = new ThongTinDinhMuc();
                        ttdm.MaHaiQuan= maHaiQuan;
                        ttdm.MaDoanhNghiep = maDoanhNghiep;
                        ttdm.MaSanPham = row["Ma_SP"].ToString();
                        ttdm.SoDinhMuc = Convert.ToInt32(row["SO_DM"].ToString());
                        ttdm.NgayDangKy = Convert.ToDateTime(row["NGAY_DK"].ToString());                        
                        try
                        {
                            ttdm.NgayApDung = Convert.ToDateTime(row["NGAY_AD"].ToString());
                        }
                        catch
                        {
                            ttdm.NgayApDung = new DateTime(1900, 1, 1);
                        }
                        try
                        {
                            ttdm.NgayHetHan = Convert.ToDateTime(row["NGAY_HH"].ToString());
                        }
                        catch
                        {
                            ttdm.NgayHetHan = new DateTime(1900, 1, 1);
                        }                        
                        ttdm.InsertUpdateTransaction(transaction);
                    }
                    foreach (DataRow row in ds.Tables[1].Rows)
                    {
                        DinhMuc dm = new DinhMuc();
                        dm.MaHaiQuan = maHaiQuan;
                        dm.MaDoanhNghiep = maDoanhNghiep;
                        dm.MaSanPHam = row["Ma_SP"].ToString();
                        dm.MaNguyenPhuLieu = row["Ma_NPL"].ToString();
                        dm.DinhMucSuDung = Convert.ToDecimal(row["DM_SD"].ToString());
                        dm.DinhMucChung = Convert.ToDecimal(row["DM_Chung"].ToString());
                        dm.GhiChu = row["GHI_CHU"].ToString();
                        dm.InsertUpdateTransaction(transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
            }
            return true;
        }
        public static void DongBoDuLieuHaiQuan(string maHaiQuan, string maDoanhNghiep, string[] masp)
        {
            SXXKService service = new SXXKService();
            DataSet ds= service.DinhMuc_GetDanhSachOfSanPham(maHaiQuan, maDoanhNghiep,masp);
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();         
            string ma = "";
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
               
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        DinhMuc dm = new DinhMuc();
                        dm.MaHaiQuan = maHaiQuan;
                        dm.MaDoanhNghiep = maDoanhNghiep;
                        dm.MaSanPHam = row["Ma_SP"].ToString();
                        if (ma != dm.MaSanPHam)
                        {
                            ma = dm.MaSanPHam;
                            dm.DeleteByForeignKeyTransaction(transaction);
                        }
                        dm.MaNguyenPhuLieu = row["Ma_NPL"].ToString();
                        dm.DinhMucSuDung = Convert.ToDecimal(row["DM_SD"].ToString());
                        dm.DinhMucChung = Convert.ToDecimal(row["DM_Chung"].ToString());
                        dm.GhiChu = row["GHI_CHU"].ToString();
                        dm.InsertUpdateTransaction(transaction);
                    }
                         transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
